package com.smartwebx.ha.dal.realtime.sqlprovider;

import static org.apache.ibatis.jdbc.SqlBuilder.BEGIN;
import static org.apache.ibatis.jdbc.SqlBuilder.FROM;
import static org.apache.ibatis.jdbc.SqlBuilder.GROUP_BY;
import static org.apache.ibatis.jdbc.SqlBuilder.INNER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT;
import static org.apache.ibatis.jdbc.SqlBuilder.SQL;
import static org.apache.ibatis.jdbc.SqlBuilder.WHERE;

import java.util.Date;
import java.util.Map;

import org.apache.commons.lang3.time.DateUtils;

import com.google.common.base.Objects;
import com.smartwebx.ha.dal.config.OracleConfigInfo;

public class SoilProvider {
	private String ST_SOIL_R = OracleConfigInfo.REALTIME_NAME + ".ST_SOIL_R";
	private String ST_STBPRP_B = OracleConfigInfo.REALTIME_NAME
			+ ".ST_STBPRP_B";
	public String getPurenessSoilProcesssSQL(Map<String,String> maps){
		String selSLMName = maps.get("selSLMName");
		BEGIN();
		SELECT("TM,"+selSLMName+" val");
		FROM(ST_SOIL_R);
		WHERE("STCD = #{stcd}");
		WHERE("TM>=#{startDate}");
		WHERE("TM<#{endDate}");
		WHERE("EXKEY = #{exKey}");
		return SQL();
	}
	
	public String getSingleStatDayAvgProcessSQL() {
		BEGIN();
		return getSQL("more");
	}

	public String getStartHourBeforeAvgValSQL() {
		BEGIN();
		return getSQL("one");
	}

	public String getAllStatMoistureSQL(Map<String, Object> maps) {
		maps = this.reParams(maps);
		BEGIN();
		String colName = (String) maps.get("selSLMName");
		String primaryTableSql = primaryTableBuild(colName);
		String slaveTableSql = slaveTableBuild();
		
		SELECT(columnToFieldMap());
		FROM("(" + primaryTableSql + ") Z");
		INNER_JOIN("(" + slaveTableSql + ") T on Z.STCD=T.STCD");

		return SQL();
	}

	private String columnToFieldMap() {
		StringBuffer sb = new StringBuffer();
		sb.append("T.LGTD,");
		sb.append("T.LTTD,");
		sb.append("Z.STCD,");
		sb.append("T.STNM,");
		sb.append("T.ADDVCD inProv,");
		sb.append("'SS' sttp,");
		sb.append("Z.Z val,");
		sb.append("T.FRGRD frgrd,");
		sb.append("Z.TM tm");
		return sb.toString();
	}

	private String primaryTableBuild(String colName) {
		SELECT("STCD");
		SELECT("MAX(TM) AS TM");
		SELECT("MAX(" + colName + ")keep(dense_rank last order by TM) AS Z");
		FROM(ST_SOIL_R);
		WHERE("TM>#{startDate}");
		WHERE("TM<=#{endDate}");
		GROUP_BY("STCD");
		return SQL();
	}

	private String slaveTableBuild() {
		SELECT("*");
		FROM(ST_STBPRP_B + " B");
		WHERE("B.STTP IN('ZZ','ZQ','PP','DD','RR','TT','SS')");
		WHERE("B.LGTD IS NOT NULL");
		WHERE("B.LTTD IS NOT NULL");
		WHERE("B.ADDVCD IS NOT NULL");
		return SQL();
	}

	private Map<String, Object> reParams(Map<String, Object> maps) {
		Date datePoint = (Date) maps.get("datePoint");
		String selSLMName=(String) maps.get("selFieldName");
		Date startDate = DateUtils.addDays(datePoint, -10);
		Date endDate = datePoint;

		maps.put("startDate", startDate);
		maps.put("endDate", endDate);
		maps.put("selSLMName", selSLMName);
		maps.remove("datePoint");

		return maps;
	}

	private String getSQL(String type) {
		SELECT("(a1+a2+a3+a4+a5+a6+a7+a8+a9+a10+a11+a12) / "
				+ "(CASE WHEN a1=0 THEN 0 WHEN a1>0 THEN 1 end +"
				+ "CASE WHEN a2=0 THEN 0 WHEN a2>0 THEN 1 end + "
				+ "CASE WHEN a3=0 THEN 0 WHEN a3>0 THEN 1 end + "
				+ "CASE WHEN a4=0 THEN 0 WHEN a4>0 THEN 1 end +"
				+ "CASE WHEN a5=0 THEN 0 WHEN a5>0 THEN 1 end +"
				+ "CASE WHEN a6=0 THEN 0 WHEN a6>0 THEN 1 end +"
				+ "CASE WHEN a7=0 THEN 0 WHEN a7>0 THEN 1 end +"
				+ "CASE WHEN a8=0 THEN 0 WHEN a8>0 THEN 1 end +"
				+ "CASE WHEN a9=0 THEN 0 WHEN a9>0 THEN 1 end +"
				+ "CASE WHEN a10=0 THEN 0 WHEN a10>0 THEN 1 end +"
				+ "CASE WHEN a11=0 THEN 0 WHEN a11>0 THEN 1 end +"
				+ "CASE WHEN a12=0 THEN 0 WHEN a12>0 THEN 1 end) val,TM ");

		String subWhere = "";
		if (Objects.equal(type, "one")) {
			subWhere = "AND TM<#{datePoint} AND ROWNUM = 1 ORDER BY TM DESC";
		} else if (Objects.equal(type, "more")) {
			subWhere = "AND TM>=#{startDate} AND TM<#{endDate}";
		}

		FROM(" (SELECT "
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SRLSLM, '^\\d+(\\.\\d+)?')), 0) a1,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM10, '^\\d+(\\.\\d+)?')), 0) a2,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM20, '^\\d+(\\.\\d+)?')), 0) a3,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM30, '^\\d+(\\.\\d+)?')), 0) a4,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM40, '^\\d+(\\.\\d+)?')), 0) a5,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM50, '^\\d+(\\.\\d+)?')), 0) a6,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM60, '^\\d+(\\.\\d+)?')), 0) a7,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM70, '^\\d+(\\.\\d+)?')), 0) a8,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM80, '^\\d+(\\.\\d+)?')), 0) a9,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM90, '^\\d+(\\.\\d+)?')), 0) a10,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM100, '^\\d+(\\.\\d+)?')), 0) a11,"
				+ "COALESCE(TO_NUMBER(REGEXP_SUBSTR(SLM110, '^\\d+(\\.\\d+)?')), 0) a12,TM "
				+ "FROM " + ST_SOIL_R + " WHERE STCD=#{stcd} "
				+ "AND to_char(TM,'HH24')=8 AND EXKEY=#{exKey} " + subWhere
				+ ")");

		return SQL();
	}
}
